package com.zbdx.dao.impl;

import com.zbdx.dao.UserDao;
import com.zbdx.domain.User;
import com.zbdx.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public User findUserByUsernameAndPassword(String usernmae, String password) throws SQLException {
        String sql = "SELECT * FROM users2 WHERE username = ? AND password = ?";
        try (Connection conn = DruidUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, usernmae);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new User(rs.getInt("id"), rs.getString("email"), rs.getString("password"));
            }
        }
        return null;
    }

    @Override
    public boolean saveUser(User user) throws SQLException {
        String sql = "INSERT INTO users2 (username, password) VALUES (?, ?)";
        try (Connection conn = DruidUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getusername());
            ps.setString(2, user.getPassword());
            return ps.executeUpdate() > 0;
        }
    }

    @Override
    public boolean Exists(String username) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users2 WHERE username = ?";
        Connection conn = DruidUtil.getConnection();
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        }
        return false;
    }

    @Override
    public User findByUsername(String username) throws SQLException {
        String sql = "SELECT * FROM users2 WHERE username = ?";
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        ResultSet rs = pstmt.executeQuery();

        User user = null;
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setusername(rs.getString("username"));
            user.setemail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
        }

        DruidUtil.close(conn);
        return user;
    }

    @Override
    public int updateUserInfo(User user) throws SQLException {
        String sql = "UPDATE users2 SET email = ?, phone = ?, address = ? WHERE username = ?";
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, user.getemail());
        pstmt.setString(2, user.getPhone());
        pstmt.setString(3, user.getAddress());
        pstmt.setString(4, user.getusername());

        int result = pstmt.executeUpdate();
        DruidUtil.close(conn);
        return result;
    }


    //修改----------------------------------------
    private QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());


    @Override
    public int insert(User user) throws SQLException {
        String sql = "INSERT INTO users2 (username, password, email, phone, address) VALUES (?, ?, ?, ?, ?)";
        Object[] param = {user.getusername(), user.getPassword(), user.getemail(), user.getPhone(), user.getAddress()};
        int res = qr.update(sql, param);
        return res;
    }

    @Override
    public int update(User user) throws SQLException {
        String sql = "update users2 set username = ?,password = ?,email = ?,phone = ?,address = ?where id = ?";
        Object[] param = {user.getusername(),user.getPassword(),user.getemail(),user.getPhone(),user.getAddress(),user.getId()};
        int res = qr.update(sql,param);
        return res;
    }

    @Override
    public int deleteById(int id) throws SQLException {
        String sql = "delete from users2 where id = ?";
        Object[] obj = {id};
        int res = qr.update(sql, obj);
        return res;
    }

    @Override
    public int bathDeleteUser(List<Integer> ids) throws SQLException {
        if (ids == null || ids.isEmpty()) {
            System.out.println("没有要删除的用户ID");
            return 0;
        }
        String sql = "delete from user where id in (";
        StringBuilder placeholders = new StringBuilder();

        for (int i = 0; i < ids.size(); i++) {
            placeholders.append("?");
            if (i < ids.size() - 1) {
                placeholders.append(", ");
            }
        }
        sql += placeholders.toString() + ")";
        // 创建参数数组
        Integer[] params = ids.toArray(new Integer[0]);
        // 调用 qr.update 方法时传递参数数组
        int res = qr.update(sql, params);
        return res;
    }

    @Override
    public User selectById(int id) throws SQLException {
        String sql = "select * from users2 where id = ?";
        Object[] obj = {id};
        User user = qr.query(sql,new BeanHandler<User>(User.class),obj);
        return user;
    }

    @Override
    public List<User> selectAll() throws SQLException {
        String sql = "select * from users2";
        //调用QueryRunner的query方法查询
        List<User> list = qr.query(sql, new BeanListHandler<User>(User.class));
        return list;
    }

    @Override
    public List<User> selectBySearch(String name) throws SQLException {
        String sql = "SELECT * FROM users2 WHERE (username LIKE ? OR username IS NULL) ";
        // 准备查询参数
        String nameParam = name.isEmpty() ? "%" : "%" + name + "%";

        System.out.println(sql);
        // 执行查询
        List<User> list = qr.query(sql, new BeanListHandler<>(User.class), nameParam);
        return list;
    }


}
